package com.flame.system.tools.service;

import com.flame.core.utils.DateUtils;
import com.flame.system.tools.controller.vo.CodeGenerateVo;
import com.flame.system.tools.controller.vo.DbDatasourceTableVo;
import com.flame.system.tools.controller.vo.DbDatasourceVo;
import com.flame.system.tools.controller.vo.FormDataVo;
import com.flame.system.tools.dialet.DbDialetFactory;
import com.flame.system.tools.entity.*;
import com.flame.system.tools.exception.SystemExceptionMessage;
import com.flame.system.tools.exception.SystemRuntimeException;
import com.flame.system.tools.mapper.DbDataSourceMapper;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.io.StringWriter;
import java.sql.*;
import java.util.*;

@Service
public class GeneratorService {

    @Autowired
    private DbDataSourceMapper dbDataSourceMapper;

    @Autowired
    private DbDialetFactory dbDialetFactory;

    public void addDbDatasource(DbDatasource dbDatasource) {
        DbDatasource datasource = dbDataSourceMapper.selectById(dbDatasource.getDatasourceName());
        if (datasource != null) {
            throw new SystemRuntimeException(SystemExceptionMessage.DATASOURCE_EXIST, dbDatasource.getDatasourceName());
        }
        dbDataSourceMapper.insert(dbDatasource);
    }

    public List<DbDatasource> pageDbDatasource(DbDatasourceVo dbDatasourceVo) {
        return dbDataSourceMapper.page(dbDatasourceVo);
    }

    public void updateDbDatasource(DbDatasource dbDataSource) {
        dbDataSourceMapper.updateById(dbDataSource);
    }

    public void deleteDbDatasourceById(DbDatasource dbDataSource) {
        dbDataSourceMapper.deleteById(dbDataSource);
    }

    public DbDatasource selectById(DbDatasourceVo dbDatasourceVo) {
        return dbDataSourceMapper.selectById(dbDatasourceVo.getDatasourceName());
    }

    public void testDatasource(DbDatasource dbDataSource) throws Exception {
        try {
            Connection connection = createDbConnection(dbDataSource);
            connection.close();
        } catch (SQLException exception) {
            throw new SystemRuntimeException(SystemExceptionMessage.TEST_FAILED, dbDataSource.getDatasourceName());
        }

    }

    public List<DbTable> listDatasourceTable(DbDatasourceVo dbDatasourceVo) throws Exception {
        DbDatasource dbDatasource = dbDataSourceMapper.selectById(dbDatasourceVo.getDatasourceName());
        Connection connection = createDbConnection(dbDatasource);
        DatabaseMetaData metaData = connection.getMetaData();
        String catalog = connection.getCatalog();
        String schema = connection.getSchema();
        List<DbTable> dbTableList = new ArrayList<>();
        ResultSet tables = metaData
                .getTables(catalog, schema, "%" + dbDatasourceVo.getTableName() + "%", new String[]{"TABLE", "VIEW"});
        while (tables.next()) {
            // TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS TYPE_CAT TYPE_SCHEM TYPE_NAME
            // SELF_REFERENCING_COL_NAME REF_GENERATION;
            DbTable dbTable = new DbTable();
            dbTable.setTableCat(tables.getString("TABLE_CAT"));
            dbTable.setTableSchem(tables.getString("TABLE_SCHEM"));
            dbTable.setTableName(tables.getString("TABLE_NAME"));
            dbTable.setTableType(tables.getString("TABLE_TYPE"));
            dbTable.setRemarks(tables.getString("REMARKS"));
            dbTable.setTypeCat(tables.getString("TYPE_CAT"));
            dbTable.setTypeSchem(tables.getString("TYPE_SCHEM"));
            dbTable.setTypeName(tables.getString("TYPE_NAME"));
            dbTable.setSelfReferencingColName(tables.getString("SELF_REFERENCING_COL_NAME"));
            dbTable.setRefGeneration(tables.getString("REF_GENERATION"));
            dbTableList.add(dbTable);
        }
        connection.close();
        return dbTableList;
    }

    public List<DbColumn> listDatasourceTableColumn(DbDatasourceTableVo dbDatasourceTableVo) throws Exception {
        DbDatasource dbDatasource = dbDataSourceMapper.selectById(dbDatasourceTableVo.getDatasourceName());
        Connection connection = createDbConnection(dbDatasource);
        DatabaseMetaData metaData = connection.getMetaData();
        String catalog = connection.getCatalog();
        String schema = connection.getSchema();

        List<DbColumn> dbTableColumnList = new ArrayList<>();
        ResultSet columns = metaData.getColumns(catalog, schema, dbDatasourceTableVo.getTableName(), null);
        while (columns.next()) {
            DbColumn dbColumn = new DbColumn();
            // TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE TYPE_NAME COLUMN_SIZE BUFFER_LENGTH
            dbColumn.setTableCat(columns.getString("TABLE_CAT"));
            dbColumn.setTableSchem(columns.getString("TABLE_SCHEM"));
            dbColumn.setTableName(columns.getString("TABLE_NAME"));
            dbColumn.setColumnName(columns.getString("COLUMN_NAME"));
            dbColumn.setDataType(columns.getInt("DATA_TYPE"));
            dbColumn.setTypeName(columns.getString("TYPE_NAME"));
            dbColumn.setColumnSize(columns.getInt("COLUMN_SIZE"));
            dbColumn.setBufferLength(columns.getInt("BUFFER_LENGTH"));
            // DECIMAL_DIGITS NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE SQL_DATETIME_SUB
            dbColumn.setDecimalDigits(columns.getInt("DECIMAL_DIGITS"));
            dbColumn.setNumPrecRadix(columns.getInt("NUM_PREC_RADIX"));
            dbColumn.setNullable(columns.getInt("NULLABLE"));
            dbColumn.setRemarks(columns.getString("REMARKS"));
            dbColumn.setColumnDef(columns.getString("COLUMN_DEF"));
            dbColumn.setSqlDataType(columns.getInt("SQL_DATA_TYPE"));
            dbColumn.setSqlDatetimeSub(columns.getInt("SQL_DATETIME_SUB"));
            // CHAR_OCTET_LENGTH ORDINAL_POSITION IS_NULLABLE SCOPE_CATALOG SCOPE_SCHEMA SCOPE_TABLE SOURCE_DATA_TYPE
            dbColumn.setCharOctetLength(columns.getInt("CHAR_OCTET_LENGTH"));
            dbColumn.setOrdinalPosition(columns.getInt("ORDINAL_POSITION"));
            dbColumn.setIsNullable(columns.getString("IS_NULLABLE"));
            dbColumn.setScopeCatalog(columns.getString("SCOPE_CATALOG"));
            dbColumn.setScopeSchema(columns.getString("SCOPE_SCHEMA"));
            dbColumn.setScopeTable(columns.getString("SCOPE_TABLE"));
            dbColumn.setSourceDataType(columns.getInt("SOURCE_DATA_TYPE"));
            // IS_AUTOINCREMENT IS_GENERATEDCOLUMN
            dbColumn.setIsAutoincrement(columns.getString("IS_AUTOINCREMENT"));
            dbColumn.setIsGeneratedcolumn(columns.getString("IS_GENERATEDCOLUMN"));
            dbTableColumnList.add(dbColumn);
        }

        ResultSet primaryKeys = metaData.getPrimaryKeys(catalog, schema, dbDatasourceTableVo.getTableName());
        while (primaryKeys.next()) {
            String primaryKeyColumnName = primaryKeys.getString("COLUMN_NAME");
            // TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME KEY_SEQ PK_NAME
            for (DbColumn dbColumn : dbTableColumnList) {
                if (dbColumn.getColumnName().equals(primaryKeyColumnName)) {
                    dbColumn.setPkName(primaryKeys.getString("PK_NAME"));
                }
            }
        }
        connection.close();
        return dbTableColumnList;
    }

    public List<GeneratedCode> getTableGeneratedCode(CodeGenerateVo codeGenerateVo) throws Exception {
        DbDatasourceVo dbDatasourceVo = new DbDatasourceVo();
        BeanUtils.copyProperties(codeGenerateVo, dbDatasourceVo);
        List<DbTable> dbTableList = this.listDatasourceTable(dbDatasourceVo);

        DbDatasourceTableVo dbDatasourceTableVo = new DbDatasourceTableVo();
        BeanUtils.copyProperties(codeGenerateVo, dbDatasourceTableVo);
        List<DbColumn> dbColumnList = this.listDatasourceTableColumn(dbDatasourceTableVo);


        DbDatasource dbDatasource = dbDataSourceMapper.selectById(dbDatasourceVo.getDatasourceName());
        Map<String, JavaClassType> convertRule = dbDialetFactory.dialet(dbDatasource.getDbType()).typeConvertRule();
        CodeGenerateData codeGenerateData = getCodeGenerateData(convertRule, codeGenerateVo, dbTableList, dbColumnList);

        VelocityContext velocityContext = new VelocityContext();
        velocityContext.put("data", codeGenerateData);

        return getGenerateCodeList(codeGenerateData, velocityContext);
    }

    private List<GeneratedCode> getGenerateCodeList(CodeGenerateData codeGenerateData,
                                                    VelocityContext velocityContext) {
        // com.xxx.xxx -> com/xxx/xxx
        String baseLocation = codeGenerateData.getBasePackage().replaceAll("\\.", "/");

        List<GeneratedCode> generatedCodeList = new ArrayList<>();

        GeneratedCode vue = new GeneratedCode();
        vue.setCode(generateCode(velocityContext, "template/Vue.vm"));
        vue.setFilename(codeGenerateData.getClassName() + ".vue");
        vue.setLocation(codeGenerateData.getClassName());
        generatedCodeList.add(vue);

        GeneratedCode controller = new GeneratedCode();
        controller.setCode(generateCode(velocityContext, "template/Controller.vm"));
        controller.setFilename(codeGenerateData.getClassName() + "Controller.java");
        controller.setLocation(codeGenerateData.getClassName() + "/src/main/java/" + baseLocation + "/controller");
        generatedCodeList.add(controller);

        GeneratedCode service = new GeneratedCode();
        service.setCode(generateCode(velocityContext, "template/Service.vm"));
        service.setFilename(codeGenerateData.getClassName() + "Service.java");
        service.setLocation(codeGenerateData.getClassName() + "/src/main/java/" + baseLocation + "/service");
        generatedCodeList.add(service);

        GeneratedCode mapper = new GeneratedCode();
        mapper.setCode(generateCode(velocityContext, "template/Mapper.vm"));
        mapper.setFilename(codeGenerateData.getClassName() + "Mapper.java");
        mapper.setLocation(codeGenerateData.getClassName() + "/src/main/java/" + baseLocation + "/mapper");
        generatedCodeList.add(mapper);

        GeneratedCode mapperXml = new GeneratedCode();
        mapperXml.setCode(generateCode(velocityContext, "template/MapperXml.vm"));
        mapperXml.setFilename(codeGenerateData.getClassName() + "Mapper.xml");
        mapperXml.setLocation(codeGenerateData.getClassName() + "/src/main/resources/mapper");
        generatedCodeList.add(mapperXml);

        GeneratedCode entity = new GeneratedCode();
        entity.setCode(generateCode(velocityContext, "template/Entity.vm"));
        entity.setFilename(codeGenerateData.getClassName() + ".java");
        entity.setLocation(codeGenerateData.getClassName() + "/src/main/java/" + baseLocation + "/entity");
        generatedCodeList.add(entity);

        GeneratedCode vo = new GeneratedCode();
        vo.setCode(generateCode(velocityContext, "template/Vo.vm"));
        vo.setFilename(codeGenerateData.getClassName() + "Vo.java");
        vo.setLocation(codeGenerateData.getClassName() + "/src/main/java/" + baseLocation + "/controller/vo");
        generatedCodeList.add(vo);

        GeneratedCode exception = new GeneratedCode();
        exception.setCode(generateCode(velocityContext, "template/RuntimeException.vm"));
        exception.setFilename(codeGenerateData.getClassName() + "RuntimeException.java");
        exception.setLocation(codeGenerateData.getClassName() + "/src/main/java/" + baseLocation + "/exception");
        generatedCodeList.add(exception);

        GeneratedCode exceptionMessage = new GeneratedCode();
        exceptionMessage.setCode(generateCode(velocityContext, "template/ExceptionMessage.vm"));
        exceptionMessage.setFilename(codeGenerateData.getClassName() + "ExceptionMessage.java");
        exceptionMessage.setLocation(codeGenerateData.getClassName() + "/src/main/java/" + baseLocation + "/exception");
        generatedCodeList.add(exceptionMessage);

        return generatedCodeList;
    }

    private String generateCode(VelocityContext velocityContext, String classpathLocation) {
        Template template = Velocity.getTemplate(classpathLocation);
        StringWriter stringWriter = new StringWriter();
        template.merge(velocityContext, stringWriter);
        return stringWriter.toString();
    }

    /**
     * DbTable{tableCat='test', tableSchem='null', tableName='phoenix_redis_connection_info', tableType='TABLE',
     * remarks='', typeCat='null', typeSchem='null', typeName='null', selfReferencingColName='null',
     * refGeneration='null'}
     * DbTable{tableCat='test', tableSchem='null', tableName='ph_db_datasource', tableType='TABLE', remarks='数据源表',
     * typeCat='null', typeSchem='null', typeName='null', selfReferencingColName='null', refGeneration='null'}
     * DbTable{tableCat='test', tableSchem='null', tableName='users', tableType='TABLE', remarks='', typeCat='null',
     * typeSchem='null', typeName='null', selfReferencingColName='null', refGeneration='null'}
     * <p>
     * DbColumn{tableCat='test', tableSchem='null', tableName='ph_db_datasource', columnName='datasource_id',
     * dataType=-5, typeName='BIGINT', columnSize=19, bufferLength=65535, decimalDigits=0, numPrecRadix=10,
     * nullable=0, remarks='id主键', columnDef='null', sqlDataType=0, sqlDatetimeSub=0, charOctetLength=0,
     * ordinalPosition=1, isNullable='NO', scopeCatalog='null', scopeSchema='null', scopeTable='null',
     * sourceDataType=0, isAutoincrement='NO', isGeneratedcolumn='NO'}
     * DbColumn{tableCat='test', tableSchem='null', tableName='ph_db_datasource', columnName='name', dataType=12,
     * typeName='VARCHAR', columnSize=32, bufferLength=65535, decimalDigits=0, numPrecRadix=10, nullable=1,
     * remarks='datasource名称', columnDef='null', sqlDataType=0, sqlDatetimeSub=0, charOctetLength=96,
     * ordinalPosition=2, isNullable='YES', scopeCatalog='null', scopeSchema='null', scopeTable='null',
     * sourceDataType=0, isAutoincrement='NO', isGeneratedcolumn='NO'}
     * DbColumn{tableCat='test', tableSchem='null', tableName='ph_db_datasource', columnName='driver_class_name',
     * dataType=12, typeName='VARCHAR', columnSize=32, bufferLength=65535, decimalDigits=0, numPrecRadix=10,
     * nullable=1, remarks='jdbc驱动', columnDef='null', sqlDataType=0, sqlDatetimeSub=0, charOctetLength=96,
     * ordinalPosition=3, isNullable='YES', scopeCatalog='null', scopeSchema='null', scopeTable='null',
     * sourceDataType=0, isAutoincrement='NO', isGeneratedcolumn='NO'}
     * DbColumn{tableCat='test', tableSchem='null', tableName='ph_db_datasource', columnName='jdbc_url', dataType=12,
     * typeName='VARCHAR', columnSize=256, bufferLength=65535, decimalDigits=0, numPrecRadix=10, nullable=1,
     * remarks='jdbc-url链接', columnDef='null', sqlDataType=0, sqlDatetimeSub=0, charOctetLength=768,
     * ordinalPosition=4, isNullable='YES', scopeCatalog='null', scopeSchema='null', scopeTable='null',
     * sourceDataType=0, isAutoincrement='NO', isGeneratedcolumn='NO'}
     * DbColumn{tableCat='test', tableSchem='null', tableName='ph_db_datasource', columnName='username', dataType=12,
     * typeName='VARCHAR', columnSize=32, bufferLength=65535, decimalDigits=0, numPrecRadix=10, nullable=1,
     * remarks='账号', columnDef='null', sqlDataType=0, sqlDatetimeSub=0, charOctetLength=96, ordinalPosition=5,
     * isNullable='YES', scopeCatalog='null', scopeSchema='null', scopeTable='null', sourceDataType=0,
     * isAutoincrement='NO', isGeneratedcolumn='NO'}
     * DbColumn{tableCat='test', tableSchem='null', tableName='ph_db_datasource', columnName='password', dataType=12,
     * typeName='VARCHAR', columnSize=32, bufferLength=65535, decimalDigits=0, numPrecRadix=10, nullable=1,
     * remarks='密码', columnDef='null', sqlDataType=0, sqlDatetimeSub=0, charOctetLength=96, ordinalPosition=6,
     * isNullable='YES', scopeCatalog='null', scopeSchema='null', scopeTable='null', sourceDataType=0,
     * isAutoincrement='NO', isGeneratedcolumn='NO'}
     */
    private CodeGenerateData getCodeGenerateData(Map<String, JavaClassType> convertRule, CodeGenerateVo codeGenerateVo,
                                                 List<DbTable> dbTableList, List<DbColumn> dbColumnList) {
        CodeGenerateData codeGenerateData = new CodeGenerateData();
        codeGenerateData.setBasePackage(codeGenerateVo.getBasePackage());
        codeGenerateData.setUrlPrefix(codeGenerateVo.getUrlPrefix());
        codeGenerateData.setAuthor(codeGenerateVo.getAuthor());
        codeGenerateData.setDatetime(DateUtils.currentDateTime());


        codeGenerateData.setTableName(dbTableList.get(0).getTableName());
        codeGenerateData.setTableRemarks(dbTableList.get(0).getRemarks());
        codeGenerateData.setClassName(getClassName(codeGenerateVo, dbTableList.get(0).getTableName()));
        codeGenerateData.setSmClassName(getSmClassName(codeGenerateVo, dbTableList.get(0).getTableName()));

        codeGenerateData.setImportList(getImportList(convertRule, dbColumnList));
        codeGenerateData.setColumnDataList(getColumnDataList(convertRule, dbColumnList, false));
        codeGenerateData.setPkColumnDataList(getColumnDataList(convertRule, dbColumnList, true));
        return codeGenerateData;
    }

    private List<CodeGenerateData.CodeGenerateColumnData> getColumnDataList(Map<String, JavaClassType> convertRule,
                                                                            List<DbColumn> dbColumnList, boolean isPk) {
        List<CodeGenerateData.CodeGenerateColumnData> list = new ArrayList<>();
        for (DbColumn dbColumn : dbColumnList) {
            CodeGenerateData.CodeGenerateColumnData columnData = null;
            if (isPk) {
                if (StringUtils.hasText(dbColumn.getPkName()) && "PRIMARY".equals(dbColumn.getPkName())) {
                    columnData = getCodeGenerateColumnData(convertRule, dbColumn);
                    list.add(columnData);
                }
            } else {
                if (!StringUtils.hasText(dbColumn.getPkName())) {
                    columnData = getCodeGenerateColumnData(convertRule, dbColumn);
                    list.add(columnData);
                }
            }
        }
        return list;
    }

    private CodeGenerateData.CodeGenerateColumnData getCodeGenerateColumnData(Map<String, JavaClassType> convertRule,
                                                                              DbColumn dbColumn) {
        CodeGenerateData.CodeGenerateColumnData columnData = new CodeGenerateData.CodeGenerateColumnData();
        columnData.setColumnName(mapUnderscoreToCamelCase(dbColumn.getColumnName().toLowerCase(), 0, false));
        columnData.setUpColumnName(mapUnderscoreToCamelCase(dbColumn.getColumnName().toLowerCase(), 0, true));
        columnData.setColumnType(convertRule.get(dbColumn.getTypeName()).getClassName());
        columnData.setColumnFullType(convertRule.get(dbColumn.getTypeName()).getClassFullName());
        columnData.setDbColumnName(dbColumn.getColumnName());
        columnData.setDbColumnTypeName(dbColumn.getTypeName());
        columnData.setColumnRemarks(dbColumn.getRemarks());
        return columnData;
    }

    private Set<String> getImportList(Map<String, JavaClassType> convertRule, List<DbColumn> dbColumnList) {
        Set<String> set = new HashSet<>();
        for (DbColumn dbColumn : dbColumnList) {
            String classFullName = convertRule.get(dbColumn.getTypeName()).getClassFullName();
            if (StringUtils.hasText(classFullName)) {
                set.add(classFullName);
            }
        }
        return set;
    }

    private String getSmClassName(CodeGenerateVo codeGenerateVo, String tableName) {
        int index = removeTablePrefix(codeGenerateVo, tableName.toLowerCase());

        return mapUnderscoreToCamelCase(tableName.toLowerCase(), index, false);
    }

    private String getClassName(CodeGenerateVo codeGenerateVo, String tableName) {
        int index = removeTablePrefix(codeGenerateVo, tableName.toLowerCase());

        return mapUnderscoreToCamelCase(tableName.toLowerCase(), index, true);
    }

    private String mapUnderscoreToCamelCase(String str, int index, boolean flag) {
        char[] chs = str.toCharArray();
        StringBuilder sb = new StringBuilder();
        while (index < chs.length) {
            if (chs[index] == '_') {
                flag = true;
                index++;
                continue;
            }
            if (flag) {
                if (chs[index] >= 'a' && chs[index] <= 'z') {
                    sb.append((char) (chs[index] - 'a' + 'A'));
                    flag = false;
                    index++;
                } else {
                    sb.append(chs[index]);
                    flag = false;
                    index++;
                }
            } else {
                sb.append(chs[index]);
                index++;
            }
        }
        return sb.toString();
    }

    private int removeTablePrefix(CodeGenerateVo codeGenerateVo, String str) {
        char[] chs = str.toCharArray();
        int index = 0;
        if (StringUtils.hasText(codeGenerateVo.getRemoveTableNamePrefix())) {
            String prefix = codeGenerateVo.getRemoveTableNamePrefix();
            for (int i = 0; i < prefix.length(); i++) {
                if (chs[index] == prefix.charAt(i)) {
                    index++;
                }
            }
        }
        return index;
    }

    private Connection createDbConnection(DbDatasource dbDatasource) throws Exception {
        Class<?> clazz = Class.forName(dbDialetFactory.dialet(dbDatasource.getDbType()).driverClassName());
        Driver driver = (Driver) clazz.newInstance();
        Properties properties = new Properties();
        properties.setProperty("user", dbDatasource.getUsername());
        properties.setProperty("password", dbDatasource.getPassword());
        // 设置可以获取remarks信息
        properties.setProperty("remarks", "true");
        // 设置可以获取tables remarks信息
        properties.setProperty("useInformationSchema", "true");
        return driver.connect(dbDatasource.getJdbcUrl(), properties);
    }

    public String generateFormCode(FormDataVo formDataVo) {
        VelocityContext velocityContext = new VelocityContext();
        velocityContext.put("data", formDataVo);
        return generateCode(velocityContext, "template/VueFormCode.vm");
    }
}
